package persistencia;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import ui.BillView;

import customexceptions.LineAlreadyExistsException;
import dto.LineBillDTO;

import model.*;

public class LineMapper {
	
	private static LineMapper s_lineMapper;

	private LineMapper() {
	}

	public static LineMapper GetInstancia() {
		if (s_lineMapper == null) {
			s_lineMapper = new LineMapper();
		}

		return s_lineMapper;
	}
	
	public TitularLine BuscarTitularLine(String lineNumber) 
	{
		Connection con = PoolConnection.getPoolConnection().getConnection();
		TitularLine line = null;
		try {
			String senten = "select CreationDate from Line where linenumber = ? and lineParent is null";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setString(1, lineNumber);
			
			ResultSet result = ps.executeQuery();
			//Titular line
			if (result.next())
			{
				String creationDate = result.getString("creationDate");				
				line = new TitularLine();
				line.populateTitularLine(creationDate,lineNumber);
			}
			//add bill
			line.populateLineBill(LineBillMapper.GetInstancia().BuscarLineBill(line.getLineNumber()));
			
			PoolConnection.getPoolConnection().realeaseConnection(con);
		} catch (SQLException e) {
			System.out.println("Mensaje Error al Insertar Usuario: "
					+ e.getMessage());
			System.out.println("Stack Trace al Insertar Usuario: "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
		return (TitularLine)line;
		
	}
	public ArrayList<TitularLine> loadTitularLine() 
	{
		Connection con = PoolConnection.getPoolConnection().getConnection();
		TitularLine line = null;
		 ArrayList<TitularLine> lines =  new  ArrayList<TitularLine>();
		try {
			String senten = "select CreationDate, linenumber, planNumber, DNI from Line where lineParent is null";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ResultSet result = ps.executeQuery();
			//Titular line
			if (result.next())
			{
				String creationDate = result.getString("creationDate");				
				line = new TitularLine();
				line.populateTitularLine(creationDate,result.getString("linenumber"));
				//add bill
				line.populateLineBill(LineBillMapper.GetInstancia().BuscarLineBill(line.getLineNumber()));
				//add Plan
				Plan plan = PlanMapper.GetInstancia().GetPlan(result.getInt("planNumber"));
				line.setPlan(plan);
				//add User
				User user = UserMapper.GetInstancia().BuscarUser(result.getString("DNI"));
				line.populateUser(user);
				//save
				lines.add(line);
				
			}
			
			
			
			PoolConnection.getPoolConnection().realeaseConnection(con);
		} catch (SQLException e) {
			System.out.println("Mensaje Error al Insertar Usuario: "
					+ e.getMessage());
			System.out.println("Stack Trace al Insertar Usuario: "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
		return lines;
		
	}
	public String BuscarTitularLineUserId(String lineNumber) 
	{
		Connection con = PoolConnection.getPoolConnection().getConnection();
		String user = null;
		try {
			String senten = "select dni from Line where linenumber = ? and lineParent is null";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setString(1, lineNumber);
			
			ResultSet result = ps.executeQuery();
			if (result.next())
			{
				user =  result.getString("dni");
				
			}

			PoolConnection.getPoolConnection().realeaseConnection(con);
		} catch (SQLException e) {
			System.out.println("Mensaje Error al Insertar Usuario: "
					+ e.getMessage());
			System.out.println("Stack Trace al Insertar Usuario: "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
		return user;
	}
	public int BuscarTitularLinePlan(String lineNumber) 
	{
		Connection con = PoolConnection.getPoolConnection().getConnection();
		int linePlan = -1;
		try {
			String senten = "select PlanNumber from Line where linenumber = ? and lineParent is null";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setString(1, lineNumber);
			
			ResultSet result = ps.executeQuery();
			if (result.next())
			{
				linePlan =  result.getInt("PlanNumber");
				
			}

			PoolConnection.getPoolConnection().realeaseConnection(con);
		} catch (SQLException e) {
			System.out.println("Mensaje Error al buscar plan de linea adicional: "
					+ e.getMessage());
			System.out.println("Stack Trace al buscar plan de linea adicional: "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
		return linePlan;
	}
	public AdditionalLine BuscarAdditionalLine(String lineNumber) 
	{
		Connection con = PoolConnection.getPoolConnection().getConnection();
		AdditionalLine line = null;
		try {
			String senten = "select AdditionalName, CreationDate, LineParent, PlanNumber from Line where linenumber = ? and lineParent is not null";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setString(1, lineNumber);
			
			ResultSet result = ps.executeQuery();
			if (result.next())
			{
				String creationDate = result.getString("creationDate");
				String additionalName = result.getString("AdditionalName");
				line = new AdditionalLine(creationDate,additionalName,lineNumber);
			}

			PoolConnection.getPoolConnection().realeaseConnection(con);
		} catch (SQLException e) {
			System.out.println("Mensaje Error al Insertar Usuario: "
					+ e.getMessage());
			System.out.println("Stack Trace al Insertar Usuario: "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
		return line;
		
	}
	public String BuscarAdditionalLineParentLine(String lineNumber) 
	{
		Connection con = PoolConnection.getPoolConnection().getConnection();
		String lineParent = null;
		try {
			String senten = "select LineParent from Line where linenumber = ? and lineParent is not null";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setString(1, lineNumber);
			
			ResultSet result = ps.executeQuery();
			if (result.next())
			{
				lineParent =  result.getString("LineParent");
				
			}

			PoolConnection.getPoolConnection().realeaseConnection(con);
		} catch (SQLException e) {
			System.out.println("Mensaje Error al Insertar Usuario: "
					+ e.getMessage());
			System.out.println("Stack Trace al Insertar Usuario: "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
		return lineParent;
	}
	public int BuscarAdditionalLinePlan(String lineNumber) 
	{
		Connection con = PoolConnection.getPoolConnection().getConnection();
		int linePlan=-1;
		try {
			String senten = "select PlanNumber from Line where linenumber = ? and lineParent is not null";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setString(1, lineNumber);
			
			ResultSet result = ps.executeQuery();
			if (result.next())
			{
				linePlan =  result.getInt("PlanNumber");
				
			}

			PoolConnection.getPoolConnection().realeaseConnection(con);
		} catch (SQLException e) {
			System.out.println("Mensaje Error al buscar plan de linea adicional: "
					+ e.getMessage());
			System.out.println("Stack Trace al buscar plan de linea adicional: "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
		return linePlan;
	}	
	
	public int GetLineUserId(String Line)
	{
		
		Connection con = PoolConnection.getPoolConnection().getConnection();
		int userId = Constants.NEW_ID;
		try {
			String senten = "select Dni from  UserToLine where linenumber = ? ";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setString(1, Line);
			
			ResultSet result = ps.executeQuery();
			if (result.next())
			{
				userId = result.getInt("Dni");
			}

			PoolConnection.getPoolConnection().realeaseConnection(con);
		} catch (SQLException e) {
			System.out.println("Mensaje Error al Insertar Usuario: "
					+ e.getMessage());
			System.out.println("Stack Trace al Insertar Usuario: "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
		return userId;
	}
	
	public int GetLinePlanId(String Line)
	{
		
		Connection con = PoolConnection.getPoolConnection().getConnection();
		int planId = Constants.NEW_ID;
		try {
			String senten = "select PlanNumber from  Line where linenumber = ? ";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setString(1, Line);
			
			ResultSet result = ps.executeQuery();
			if (result.next())
			{
				planId = result.getInt("PlanNumber");
			}

			PoolConnection.getPoolConnection().realeaseConnection(con);
		} catch (SQLException e) {
			System.out.println("Mensaje Error al Insertar Usuario: "
					+ e.getMessage());
			System.out.println("Stack Trace al Insertar Usuario: "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
		return planId;
	}
	
	public int GetLineParentLineId(String Line)
	{
		
		Connection con = PoolConnection.getPoolConnection().getConnection();
		int parentLine = Constants.NEW_ID;
		try {
			String senten = "select LineParent from  Line where linenumber = ? ";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setString(1, Line);
			
			ResultSet result = ps.executeQuery();
			if (result.next())
			{
				parentLine = result.getInt("LineParent");
			}

			PoolConnection.getPoolConnection().realeaseConnection(con);
		} catch (SQLException e) {
			System.out.println("Mensaje Error al Insertar Usuario: "
					+ e.getMessage());
			System.out.println("Stack Trace al Insertar Usuario: "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
		return parentLine;
	}
	
	public TitularLine BuscarTitularLine(String lineNumber, ArrayList<User> users, ArrayList<Plan> planes) throws LineAlreadyExistsException
	{
		Connection con = PoolConnection.getPoolConnection().getConnection();
		TitularLine line = null;
		try {
			String senten = "select LineNumber, CreationDate, planNumber, Dni from Line left outer join UserToLine on Line.LineNumber = User.LineNumber where linenumber = ? lineParent is null";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setString(1, lineNumber);
			
			ResultSet result = ps.executeQuery();
			if (result.next())
			{
				
				String dni = result.getString("dni");
				int plan = result.getInt("planNumber");
				
				line = new TitularLine(BuscarUser(dni, users), lineNumber);
				line.setPlan(BuscarPlan(plan,planes));
			}

			PoolConnection.getPoolConnection().realeaseConnection(con);
		} catch (SQLException e) {
			System.out.println("Mensaje Error al Insertar Usuario: "
					+ e.getMessage());
			System.out.println("Stack Trace al Insertar Usuario: "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
		return line;
		
	}
	
	public AdditionalLine BuscarAdditionalLine(String lineNumber,ArrayList<Line> mainLines, ArrayList<User> users, ArrayList<Plan> planes) 
	{
		Connection con = PoolConnection.getPoolConnection().getConnection();
		AdditionalLine line = null;
		try {
			String senten = "select AdditionalName, lineParent, CreationDate, planNumber, Dni from Line left outer join User on Line.LineNumber = User.LineNumber where linenumber = ? lineParent is not null";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setString(1, lineNumber);
			
			ResultSet result = ps.executeQuery();
			if (result.next())
			{
				String AdditionalName = result.getString("AdditionalName");
				String dni = result.getString("dni");
				int plan = result.getInt("planNumber");
				String lineParent = result.getString("lineParent");
				line = new AdditionalLine(BuscarPlan(plan,planes),BuscarUser(dni, users),BuscarLine(lineParent,mainLines) ,AdditionalName, lineNumber);
			}

			PoolConnection.getPoolConnection().realeaseConnection(con);
		} catch (SQLException e) {
			System.out.println("Mensaje Error al Insertar Usuario: "
					+ e.getMessage());
			System.out.println("Stack Trace al Insertar Usuario: "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
		return line;
		
	}
	
	
	private User BuscarUser(String dni,ArrayList<User> users)
	{
		for (User user : users) {
			if (user.getDni() == dni)
				return user;
		}
		return null;
		
	}
	
	private Line BuscarLine(String lineNum,ArrayList<Line> lines)
	{
		for (Line line : lines) {
			if (line.getLineNumber()==lineNum)
				return line;
		}
		return null;
		
	}
	
	private Plan BuscarPlan(int plannumber,ArrayList<Plan> planes)
	{
		for (Plan plan : planes) {
			if (plan.getPlanNumber() == plannumber)
				return plan;
		}
		return null;
		
	}
	
	public void InsertNewLineTitular(TitularLine tl)
	{
		
		Connection con = PoolConnection.getPoolConnection().getConnection();

		try {
			String senten = "INSERT INTO Line (LineNumber, CreationDate, PlanNumber) VALUES (?,?,?)";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setString(1, tl.getLineNumber());
			ps.setString(2, tl.getCreationDate());
			ps.setInt(3, tl.getPlan().getPlanNumber());

			ps.execute();

			senten = "INSERT INTO UserToLine (LineNumber, Dni) VALUES (?,?)";
			ps = null;
			ps = con.prepareStatement(senten);
			ps.setString(1, tl.getLineNumber());
			ps.setString(2, tl.getUser());
		
			ps.execute();
			
			PoolConnection.getPoolConnection().realeaseConnection(con);
		} catch (SQLException e) {
			System.out.println("Mensaje Error al Insertar Usuario: "
					+ e.getMessage());
			System.out.println("Stack Trace al Insertar Usuario: "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
	}
	
	public void InsertInvoice(LineBillDTO line){
		Connection con = PoolConnection.getPoolConnection().getConnection();

		try {
			String senten = "INSERT INTO HistorycBills (LineNumber,Date,UserName,TotalCost,Details) VALUES (?,?,?,?,?)";

			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setString(1,line.getLineNumber());
			java.sql.Timestamp timestamp; 
			timestamp =  new java.sql.Timestamp(line.getDate().getTime());				
			ps.setTimestamp(2, timestamp);
			ps.setString(3, line.getUserName());
			ps.setString(4, line.getTotalCost());
			ps.setString(5, line.getBillDetails());
			ps.execute();
			PoolConnection.getPoolConnection().realeaseConnection(con);
		} catch (SQLException e) {
			System.out.println("Message error during persist invoiced bill: "
					+ e.getMessage());
			System.out.println("Stack Trace for error during persist invoiced bill: "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
		
	}
	public void InsertNewAditionalLine(AdditionalLine ad, String parentLine)
	{
		
		Connection con = PoolConnection.getPoolConnection().getConnection();

		try {
			String senten = "INSERT INTO Line (LineNumber, CreationDate, PlanNumber, LineParent, AdditionalName) VALUES (?,?,?,?,?)";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setString(1, ad.getLineNumber());
			ps.setString(2, ad.getCreationDate());
			ps.setInt(3, ad.getPlan().getPlanNumber());
			ps.setString(4, parentLine);
			ps.setString(5, ad.getAdditionalName());

			ps.execute();

			senten = "INSERT INTO UserToLine (LineNumber, Dni) VALUES (?,?)";
			ps = null;
			ps = con.prepareStatement(senten);
			ps.setString(1, ad.getLineNumber());
			ps.setString(2, ((TitularLine)ad.principalLine).getUser());
		
			ps.execute();
			PoolConnection.getPoolConnection().realeaseConnection(con);
		} catch (SQLException e) {
			System.out.println("Mensaje Error al Insertar Usuario: "
					+ e.getMessage());
			System.out.println("Stack Trace al Insertar Usuario: "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
	}

}
